CREATE procedure [dbo].[BAECustomerFindPendingRecord] @accountpendingid varchar(10),
     @firstName varchar(20),
     @lastName varchar(30),
     @zip varchar(10),
     @email varchar(100) as
IF @email <> ''  --Find user by email
    BEGIN
        SELECT DISTINCT AccountPendingID
        FROM AccountPending n
        WHERE UPPER(n.Email) = UPPER(@email);
    END
    ELSE IF (@accountpendingid <> '' AND @lastName <> '')  --Find user by id AND last name
    BEGIN
        SELECT DISTINCT AccountPendingID
        FROM AccountPending n
        WHERE n.AccountPendingID = @accountpendingid AND UPPER(n.LastName) = UPPER(@lastName);
    END
    ELSE IF (@zip <> '' AND @firstName <> '' AND @lastName <> '') --Find user by zip, last, first name
    BEGIN
        SELECT DISTINCT AccountPendingID
        FROM AccountPending n
        WHERE UPPER(n.PostalCode) like UPPER(@zip) + '%' AND UPPER(n.LastName) = UPPER(@lastName) AND (UPPER(n.Informal) = UPPER(@firstName) OR UPPER(n.FirstName) = UPPER(@firstName) OR UPPER(n.MiddleName) = UPPER(@firstName))
        UNION
        SELECT DISTINCT n.AccountPendingID
        FROM AccountPending n
        INNER JOIN Name_Address na ON na.ID = n.AccountPendingID
        WHERE UPPER(na.ZIP) like UPPER(@zip) + '%' AND UPPER(n.LastName) = UPPER(@lastName) AND (UPPER(n.Informal) = UPPER(@firstName) OR UPPER(n.FirstName) = UPPER(@firstName) OR UPPER(n.MiddleName) = UPPER(@firstName))
    END

GO